﻿CREATE FUNCTION [dbo].[SplitStringByDelimiter](
	@text nvarchar(max),
	@delimiter nvarchar(8) = '##'
)
RETURNS @Strings TABLE
(
id int IDENTITY PRIMARY KEY,
value nvarchar(max)
)
AS
BEGIN
	DECLARE @INDEX int
  SET @INDEX = -1
  
	WHILE (LEN(@text) > 0)
	BEGIN
		SET @INDEX = CHARINDEX(@delimiter , @text)
		
		IF (@INDEX = 0) AND (LEN(@text) > 0)
		BEGIN
			 INSERT INTO @Strings VALUES (@text)
			 BREAK
		END

		IF (@INDEX > 1)
		BEGIN
			 INSERT INTO @Strings VALUES (LEFT(@text, @INDEX - 1))
			 SET @text = RIGHT(@text, (LEN(@text) - (@INDEX - 1 + LEN(@delimiter))))
		END
		ELSE
		BEGIN
			 SET @text = RIGHT(@text, (LEN(@text) - (@INDEX - 1 + LEN(@delimiter))))
		END
	END
	
  RETURN
END